#! /bin/bash
HIVE_HOME=/usr/bin/hive
${HIVE_HOME} -S -e "
use edu_dwd;
-- insert table 语句

/*

清洗：DWD只保留未被删除的、customer不为空的、并且必须是已支付'PAID'（已报名）数据。
转换：
1. 如果origin_type是NETSERVICE和PRESIGNUP类型，即为1线上，否则为0线下。
2. 支付时间切割获取到年月日时的字符值，并分别存为单独的字段方便后续统计。
*/


--分区
SET hive.exec.dynamic.partition=true;
SET hive.exec.dynamic.partition.mode=nonstrict;
set hive.exec.max.dynamic.partitions.pernode=10000;
set hive.exec.max.dynamic.partitions=100000;
set hive.exec.max.created.files=150000;
--hive压缩
set hive.exec.compress.intermediate=true;
set hive.exec.compress.output=true;
--写入时压缩生效
set hive.exec.orc.compression.strategy=COMPRESSION;
--分桶
set hive.enforce.bucketing=true;
set hive.enforce.sorting=true;
set hive.optimize.bucketmapjoin = true;
set hive.auto.convert.sortmerge.join=true;
set hive.auto.convert.sortmerge.join.noconditionaltask=true;
--并行执行
set hive.exec.parallel=true;
set hive.exec.parallel.thread.number=8;
--矢量化查询
set hive.vectorized.execution.enabled=true;
--读取零拷贝
set hive.exec.orc.zerocopy=true;

INSERT INTO edu_dwd.customer_signup_dwd PARTITION(payment_time_year, payment_time_month, payment_time_day)
SELECT
    id,
    customer_id,
    nvl(origin_type, -1) origin_type,
    payment_time,
    substr(payment_time, 12, 2) as payment_time_hour,
    nvl(itcast_clazz_id, -1) itcast_clazz_id,
    nvl(creator, -1) creator,
    if(origin_type='NETSERVICE' or origin_type='PRESIGNUP', '1', '0') origin_type_state,
    substr(payment_time, 1, 4) payment_time_year,
    substr(payment_time, 6, 2) payment_time_month,
    substr(payment_time, 9, 2) payment_time_day
from edu_ods.customer_relationship tablesample (bucket 1 out of 10 on id)
WHERE deleted = 0 AND customer_id IS NOT NULL AND payment_state='PAID';

/*
-- 意向报名用户分析
在DWD层对customer_relationship意向客户事实表做清洗转换：
清洗掉已删除的数据；
判断学校id和学科id，空值统一转换为-1；
将origin_type来源渠道字段转换为线上/线下，如果origin_type是NETSERVICE和PRESIGNUP类型，即为1线上，否则为0线下。
*/

--分区
SET hive.exec.dynamic.partition=true;
SET hive.exec.dynamic.partition.mode=nonstrict;
set hive.exec.max.dynamic.partitions.pernode=10000;
set hive.exec.max.dynamic.partitions=100000;
set hive.exec.max.created.files=150000;
--hive压缩
set hive.exec.compress.intermediate=true;
set hive.exec.compress.output=true;
--写入时压缩生效
set hive.exec.orc.compression.strategy=COMPRESSION;
--分桶
set hive.enforce.bucketing=true;
set hive.enforce.sorting=true;
set hive.optimize.bucketmapjoin = true;
set hive.auto.convert.sortmerge.join=true;
set hive.auto.convert.sortmerge.join.noconditionaltask=true;

/*
insert into table edu_dwd.customer_intention_dwd partition (yearinfo,monthinfo,dayinfo)
select
    cr.id as rid,
    cr.customer_id,
    cr.create_date_time,
    cr.deleted,
    cr.origin_type,
    if((cr.itcast_school_id is null) or (cr.itcast_school_id = 0), -1, cr.itcast_school_id) as itcast_school_id,
    if((cr.itcast_subject_id is null) or (cr.itcast_subject_id = 0), -1, cr.itcast_subject_id) as itcast_subject_id,
    nvl(creator, -1) creator,
    substr(cr.create_date_time, 12, 2) hourinfo,
    if(cr.origin_type='NETSERVICE', '1', if(cr.origin_type='PRESIGNUP', '1', '0')) as origin_type_state,
    substr(cr.create_date_time, 1, 4) yearinfo,
    substr(cr.create_date_time, 6, 2) monthinfo,
    substr(cr.create_date_time, 9, 2) dayinfo
from edu_ods.customer_relationship_dwd tablesample(bucket 1 out of 10 on id) cr
where cr.deleted = 0
*/
-- 报错 [08S01][2] Error while processing statement:
-- FAILED: Execution Error, return code 2 from org.apache.hadoop.hive.ql.exec.mr.MapRedTask

-- 报错，动态分区
-- 提高动态分区数量和文件数量，在sql前添加
-- set hive.exec.max.dynamic.partitions.pernode=10000;
-- set hive.exec.max.dynamic.partitions=100000;
-- set hive.exec.max.created.files=150000;

-- 报错 [08S01][2] Error while processing statement:
-- FAILED: Execution Error, return code 2 from org.apache.hadoop.hive.ql.exec.mr.MapRedTask

-- 可以通过分区和分桶采样的方式。
-- 分区针对的是固定日期，而分桶采样则侧重抽查，更具有代表性。
-- 由于第一次是全量抽取数据，所以日期分区下的数据非常庞大，此时使用分桶来进行采样测试可以提升开发和测试效率。
-- 注意tablesample关键字所在的位置，是在表名之后，别名之前。
-- tablesample(bucket 1 out of 10 on id)

-- 内存不足解决方法
-- 查看各个年份数据分布情况：
select
    count(1),
    substr(create_date_time, 1, 4)
from edu_ods.customer_relationship
group by substr(create_date_time, 1, 4);

-- 从结果可以看出，数据按年分配比较均匀，因此可以按照年份来进行分批计算。

insert into table edu_dwd.customer_intention_dwd partition (yearinfo,monthinfo,dayinfo)
select
    cr.id as rid,
    cr.customer_id,
    cr.create_date_time,
    cr.deleted,
    cr.origin_type,
    if((cr.itcast_school_id is null) or (cr.itcast_school_id = 0), -1, cr.itcast_school_id) as itcast_school_id,
    if((cr.itcast_subject_id is null) or (cr.itcast_subject_id = 0), -1, cr.itcast_subject_id) as itcast_subject_id,
    nvl(creator, -1) creator,
    substr(cr.create_date_time, 12, 2) hourinfo,
    if(cr.origin_type='NETSERVICE', '1', if(cr.origin_type='PRESIGNUP', '1', '0')) as origin_type_state,
    substr(cr.create_date_time, 1, 4) yearinfo,
    substr(cr.create_date_time, 6, 2) monthinfo,
    substr(cr.create_date_time, 9, 2) dayinfo
from edu_ods.customer_relationship tablesample(bucket 1 out of 10 on id) cr
where cr.deleted = 0
and cr.create_date_time between '2011-01-01 00:00:00' and '2012-01-01 00:00:00';

insert into table edu_dwd.customer_intention_dwd partition (yearinfo,monthinfo,dayinfo)
select
    cr.id as rid,
    cr.customer_id,
    cr.create_date_time,
    cr.deleted,
    cr.origin_type,
    if((cr.itcast_school_id is null) or (cr.itcast_school_id = 0), -1, cr.itcast_school_id) as itcast_school_id,
    if((cr.itcast_subject_id is null) or (cr.itcast_subject_id = 0), -1, cr.itcast_subject_id) as itcast_subject_id,
    nvl(creator, -1) creator,
    substr(cr.create_date_time, 12, 2) hourinfo,
    if(cr.origin_type='NETSERVICE', '1', if(cr.origin_type='PRESIGNUP', '1', '0')) as origin_type_state,
    substr(cr.create_date_time, 1, 4) yearinfo,
    substr(cr.create_date_time, 6, 2) monthinfo,
    substr(cr.create_date_time, 9, 2) dayinfo
from edu_ods.customer_relationship tablesample(bucket 1 out of 10 on id) cr
where cr.deleted = 0
and cr.create_date_time between '2012-01-01 00:00:00' and '2013-01-01 00:00:00';

insert into table edu_dwd.customer_intention_dwd partition (yearinfo,monthinfo,dayinfo)
select
    cr.id as rid,
    cr.customer_id,
    cr.create_date_time,
    cr.deleted,
    cr.origin_type,
    if((cr.itcast_school_id is null) or (cr.itcast_school_id = 0), -1, cr.itcast_school_id) as itcast_school_id,
    if((cr.itcast_subject_id is null) or (cr.itcast_subject_id = 0), -1, cr.itcast_subject_id) as itcast_subject_id,
    nvl(creator, -1) creator,
    substr(cr.create_date_time, 12, 2) hourinfo,
    if(cr.origin_type='NETSERVICE', '1', if(cr.origin_type='PRESIGNUP', '1', '0')) as origin_type_state,
    substr(cr.create_date_time, 1, 4) yearinfo,
    substr(cr.create_date_time, 6, 2) monthinfo,
    substr(cr.create_date_time, 9, 2) dayinfo
from edu_ods.customer_relationship tablesample(bucket 1 out of 10 on id) cr
where cr.deleted = 0
and cr.create_date_time between '2013-01-01 00:00:00' and '2014-01-01 00:00:00';

insert into table edu_dwd.customer_intention_dwd partition (yearinfo,monthinfo,dayinfo)
select
    cr.id as rid,
    cr.customer_id,
    cr.create_date_time,
    cr.deleted,
    cr.origin_type,
    if((cr.itcast_school_id is null) or (cr.itcast_school_id = 0), -1, cr.itcast_school_id) as itcast_school_id,
    if((cr.itcast_subject_id is null) or (cr.itcast_subject_id = 0), -1, cr.itcast_subject_id) as itcast_subject_id,
    nvl(creator, -1) creator,
    substr(cr.create_date_time, 12, 2) hourinfo,
    if(cr.origin_type='NETSERVICE', '1', if(cr.origin_type='PRESIGNUP', '1', '0')) as origin_type_state,
    substr(cr.create_date_time, 1, 4) yearinfo,
    substr(cr.create_date_time, 6, 2) monthinfo,
    substr(cr.create_date_time, 9, 2) dayinfo
from edu_ods.customer_relationship tablesample(bucket 1 out of 10 on id) cr
where cr.deleted = 0
and cr.create_date_time between '2014-01-01 00:00:00' and '2015-01-01 00:00:00';

insert into table edu_dwd.customer_intention_dwd partition (yearinfo,monthinfo,dayinfo)
select
    cr.id as rid,
    cr.customer_id,
    cr.create_date_time,
    cr.deleted,
    cr.origin_type,
    if((cr.itcast_school_id is null) or (cr.itcast_school_id = 0), -1, cr.itcast_school_id) as itcast_school_id,
    if((cr.itcast_subject_id is null) or (cr.itcast_subject_id = 0), -1, cr.itcast_subject_id) as itcast_subject_id,
    nvl(creator, -1) creator,
    substr(cr.create_date_time, 12, 2) hourinfo,
    if(cr.origin_type='NETSERVICE', '1', if(cr.origin_type='PRESIGNUP', '1', '0')) as origin_type_state,
    substr(cr.create_date_time, 1, 4) yearinfo,
    substr(cr.create_date_time, 6, 2) monthinfo,
    substr(cr.create_date_time, 9, 2) dayinfo
from edu_ods.customer_relationship tablesample(bucket 1 out of 10 on id) cr
where cr.deleted = 0
and cr.create_date_time between '2015-01-01 00:00:00' and '2016-01-01 00:00:00';

insert into table edu_dwd.customer_intention_dwd partition (yearinfo,monthinfo,dayinfo)
select
    cr.id as rid,
    cr.customer_id,
    cr.create_date_time,
    cr.deleted,
    cr.origin_type,
    if((cr.itcast_school_id is null) or (cr.itcast_school_id = 0), -1, cr.itcast_school_id) as itcast_school_id,
    if((cr.itcast_subject_id is null) or (cr.itcast_subject_id = 0), -1, cr.itcast_subject_id) as itcast_subject_id,
    nvl(creator, -1) creator,
    substr(cr.create_date_time, 12, 2) hourinfo,
    if(cr.origin_type='NETSERVICE', '1', if(cr.origin_type='PRESIGNUP', '1', '0')) as origin_type_state,
    substr(cr.create_date_time, 1, 4) yearinfo,
    substr(cr.create_date_time, 6, 2) monthinfo,
    substr(cr.create_date_time, 9, 2) dayinfo
from edu_ods.customer_relationship tablesample(bucket 1 out of 10 on id) cr
where cr.deleted = 0
and cr.create_date_time between '2016-01-01 00:00:00' and '2017-01-01 00:00:00';


-- 有效线索用户分析
--分区
SET hive.exec.dynamic.partition=true;
SET hive.exec.dynamic.partition.mode=nonstrict;
set hive.exec.max.dynamic.partitions.pernode=10000;
set hive.exec.max.dynamic.partitions=100000;
set hive.exec.max.created.files=150000;
--hive压缩
set hive.exec.compress.intermediate=true;
set hive.exec.compress.output=true;
--写入时压缩生效
set hive.exec.orc.compression.strategy=COMPRESSION;
--分桶
set hive.enforce.bucketing=true;
set hive.enforce.sorting=true;
set hive.optimize.bucketmapjoin = true;
set hive.auto.convert.sortmerge.join=true;
set hive.auto.convert.sortmerge.join.noconditionaltask=true;
--并行执行
set hive.exec.parallel=true;
set hive.exec.parallel.thread.number=8;

--  有效线索报名转化率
-- customer_clue 客户线索表

INSERT INTO edu_dwd.customer_clue_dwd PARTITION (yearinfo, monthinfo, dayinfo)
SELECT
    cc.id,
    nvl(cc.customer_relationship_id, -1) customer_relationship_id,
    if(cr.origin_type='NETSERVICE' or cr.origin_type='PRESIGNUP', '1', '0') as origin_type_state,
    if(cc.clue_state='VALID_NEW_CLUES', 1, if(cc.clue_state='VALID_PUBLIC_NEW_CLUE', 2, 0)) as for_new_user,
    cc.deleted,
    cc.create_date_time,
    substr(cc.create_date_time, 12, 2) as hourinfo,
    substr(cc.create_date_time, 1, 4) as yearinfo,
    substr(cc.create_date_time, 6, 2) as monthinfo,
    substr(cc.create_date_time, 9, 2) as dayinfo
FROM edu_ods.customer_clue TABLESAMPLE(BUCKET 1 OUT OF 10 ON customer_relationship_id) cc
LEFT JOIN edu_ods.customer_relationship cr
    on cc.customer_relationship_id = cr.id
where cc.clue_state is not null AND cc.deleted = 'false';






"